﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using Utility;
using Utility.Helper;

namespace BLL
{
    public class QS_PLANWEEKAPPLYBLL : EntityBLL<QS_PLANWEEKAPPLY>, IApplyWeeekService
    {
        /// <summary>
        /// 判断在此周计划前，是否有周计划正在办理中
        /// </summary>
        /// <param name="year">计划年</param>
        /// <param name="month">计划月</param>
        /// <param name="week">当前计划周</param>
        /// <param name="type">0,1</param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable BindSelectBy(string year, string month, string week, string comid, int type, string logname)
        {
            string sql = @"select *
  from qs_planweekapply t
 where t.n_year = " + year + @"
   and t.n_month = " + month + @"

   and t.n_company_id=" + comid;

            if (type == 0)
            {
                sql += "  and t.n_week < " + week + @"  and t.n_business_state = 1";
            }
            else
            {
                sql += "  and t.n_week = " + week + @"  and t.n_business_state <>0 ";
            }

            DataTable dt = base.Query(sql, "", "BindSelectBy", logname, logname);
            return dt;
        }

        public DataTable GetDataSyBind(string comid, string pcomid, string year, string month, string week, string logname)
        {
            string sql = @"select t.n_proid,
       t.c_proname,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       t.n_type,
       nvl(a.jh, 0) syjh,
       nvl(b.mje, 0) mjeh,
       t.c_pronum,
       t.N_TYPE2,
     --  decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type1,
       t.N_IF_DETAILS IF_DETAILS,
       t.C_DETAILS_CODE DETAILS_CODE
  from qs_project t
  LEFT OUTER JOIN ( select d.n_proid, sum(d.N_USEAMOUNT) jh
                     from qs_planweekapply t, qs_planweek_detail d
                    where t.n_weekapp_id = d.n_weekapp_id
                      and t.n_business_state = 2
                      and t.n_year = " + year + @"
                      and t.n_month = " + month + @"
                      and t.n_company_id = " + comid + @"
                      and t.N_WEEK<" + week + @"
                    group by d.n_proid ) a
    on t.n_proid = a.n_proid
     LEFT OUTER JOIN (select d.n_proid, sum(d.n_amount) mje
                     from qs_planmonthapply t, qs_planmonth_detail d
                    where t.n_monthapp_id = d.n_monthapp_id
                      and t.n_business_state = 2
                      and t.n_year = " + year + @"
                      and t.n_month =  " + month + @"
                      and t.n_company_id = " + comid + @"
                    group by d.n_proid) b
    on t.n_proid = b.n_proid
where t.cmy_id=" + pcomid + @"
 order by t.n_parentid, t.n_num1, t.n_num2 ";

            DataTable dt = base.Query(sql, "", "", logname, logname);

            return dt;
        }

        public decimal GetMoneyByDetails(int applyId, int proId)
        {
            var sql = "SELECT T1.N_AMOUNT FROM QS_PLANWEEK_DETAIL T1 WHERE T1.N_WEEKAPP_ID=" + applyId + " AND T1.N_PROID=" + proId;
            var dt = Query(sql);
            if (dt.Rows.Count > 0)
            {
                return Convert.ToDecimal(dt.Rows[0][0]);
            }
            return 0;
        }

        /// <summary>
        /// 周资金明细查询
        /// </summary>
        /// <param name="buid"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable GetDataByBid(int year, int month, decimal comid, string logname, string orderStr, string orderTypeStr)
        {
            string orderstrtype = "";
            if (orderStr != null)
            {
                orderstrtype = " order by " + orderStr + " " + orderTypeStr;
            }

            string sql = @"select *
  from (select t.n_weekapp_id,
       t.n_business_id,
       c.cmy_name,
       t.c_week_name,
       t.n_week,
       sum(decode(p.N_TYPE2, 1, d.n_amount, 0)) 流入合计,
       sum(decode(p.N_TYPE2, 2, d.n_amount, 0)) 流出合计
  from qs_planweekapply   t,
       sys_company        c,
       qs_planweek_detail d,
       qs_project         p
 where t.n_company_id = c.cmy_id
   and t.n_weekapp_id = d.n_weekapp_id
   and d.n_proid = p.n_proid
   and t.n_business_state = 2
   and d.n_type = 0
   and t.n_year = " + year + @"
   and t.n_month = " + month + @"
 and t.n_company_id in (
select sc.cmy_id
  from sys_company sc
 start with sc.cmy_id = " + comid + @"
connect by prior sc.cmy_id = sc.cmy_parentid)
 group by c.cmy_name,
          t.c_week_name,
          t.n_week,
          t.n_weekapp_id,
          t.n_business_id" + orderstrtype + @")
union all
select null,
       0,
       '合计',
       '',
       null,
       sum(decode(p.N_TYPE2, 1, d.n_amount, 0)) 流入合计,
       sum(decode(p.N_TYPE2, 2, d.n_amount, 0)) 流出合计
  from qs_planweekapply   t,
       sys_company        c,
       qs_planweek_detail d,
       qs_project         p
 where t.n_company_id = c.cmy_id
   and t.n_weekapp_id = d.n_weekapp_id
   and d.n_proid = p.n_proid
   and t.n_business_state = 2
   and d.n_type = 0
   and t.n_year =  " + year + @"
   and t.n_month = " + month + @"
   and t.n_company_id in
       (select sc.cmy_id
          from sys_company sc
         start with sc.cmy_id = " + comid + @"
        connect by prior sc.cmy_id = sc.cmy_parentid)
";
            DataTable dt = base.Query(sql, "", "", logname, logname);

            return dt;
        }

        public DataTable GetDataByBid(string buid, string logname)
        {
            var sql = @"SELECT
	                        T1.N_WEEKDETAIL_ID,
	                        T1.N_WEEKAPP_ID,
	                        T1.N_PROID,
	                        T1.C_PRONAME,
                            T1.N_TYPE,
                            T1.N_IF_DETAILS AS IF_DETAILS,
                            T1.C_DETAILS_CODE AS DETAILS_CODE,
                            T1.N_PARENTID,
                            T1.C_PROWEEKNUM,
                            T1.C_REMARK,
                            T1.C_REMARK2,
	                        ROUND(NVL(T1.N_MONTHAMOUNT,0),6) AS N_MONTHAMOUNT,
	                        ROUND(NVL(T1.N_SYAMOUNT,0),6) AS N_SYAMOUNT,
	                        ROUND(NVL(T1.N_AMOUNT,0),6) AS N_AMOUNT,
	                        ROUND(NVL(T1.N_AMOUNT1,0),6) AS N_AMOUNT1,
	                        ROUND(NVL(T1.N_AMOUNT2,0),6) AS N_AMOUNT2,
	                        ROUND(NVL(T1.N_USEAMOUNT,0),6) AS N_USEAMOUNT,
	                        CASE
		                        WHEN T1.N_AMOUNT=0 THEN '0%'
		                        WHEN T1.N_AMOUNT IS NULL THEN '0%'
		                        WHEN T1.N_USEAMOUNT=0 THEN '0%'
		                        WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
		                        ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
	                        END AS N_AMOUNTPROBABILITY
                        FROM QS_PLANWEEK_DETAIL T1
                        WHERE
	                        T1.N_BUSINESS_ID = :BUSINESS_ID
                        ORDER BY
	                        T1.N_WEEKDETAIL_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":BUSINESS_ID", Convert.ToInt32(buid), "int");
            return Query(sql, parms);
            sql = @"select t.n_proid,
       t.n_weekdetail_id,
       t.n_weekapp_id,
       t.c_proname,
       decode(t.n_monthamount, 0, null, n_monthamount) n_monthamount,
       to_char(decode(t.N_SYAMOUNT, 0, null, N_SYAMOUNT),'FM9999999999.00') N_SYAMOUNT,
       to_char(decode(t.n_amount, 0, null, n_amount),'FM9999999999.00') n_amount,
       to_char(decode(t.n_amount1, 0, null, n_amount1), 'FM9999999999.00') n_amount1,
       to_char(decode(t.n_amount2, 0, null, n_amount2), 'FM9999999999.00') n_amount2,
       round(decode(t.n_useamount, 0, null, n_useamount),2) n_useamount,
       decode(n_amount,0,null,null,null,round(n_useamount/n_amount*100,2)||'%') n_amountProbability,
       t.n_type,
       t.N_IF_DETAILS IF_DETAILS,
       t.C_DETAILS_CODE DETAILS_CODE,
       pro.N_PARENTID,
       pro.N_NUM1,
       pro.N_NUM2,
       t.c_proweeknum,
       round(decode(n_amount,0,0,(n_useamount / n_amount)) , 2) * 100 || '%' amountpercent,
       t.c_remark,t.c_remark2
  from qs_planweek_detail t, qs_project pro
 where t.n_proid = pro.n_proid and t.n_business_id=" + buid;

            DataTable dt = base.Query(sql, "", "", logname, logname);
            /*********递归排序**********/
            var dt1 = dt.Clone();
            Inno(dt1, dt, 0);
            return dt1;
        }

        private void Inno(DataTable t1, DataTable t2, int pid)
        {
            foreach (var row in t2.AsEnumerable().Where(a => Convert.ToInt32(a["N_PARENTID"]) == pid).OrderBy(a => Convert.ToInt32(a["N_NUM1"])).ThenBy(a => Convert.ToInt32(a["N_NUM2"])))
            {
                var entity = t1.NewRow();
                entity["n_proid"] = row["n_proid"];
                entity["n_weekdetail_id"] = row["n_weekdetail_id"];
                entity["n_weekapp_id"] = row["n_weekapp_id"];
                entity["c_proname"] = row["c_proname"];
                entity["n_amount"] = row["n_amount"];
                entity["n_type"] = row["n_type"];
                entity["n_amount"] = row["n_amount"];
                entity["N_SYAMOUNT"] = row["N_SYAMOUNT"];
                entity["n_useamount"] = row["n_useamount"];
                entity["n_monthamount"] = row["n_monthamount"];
                entity["c_proweeknum"] = row["c_proweeknum"];
                entity["c_remark"] = row["c_remark"];
                entity["IF_DETAILS"] = row["IF_DETAILS"];
                entity["DETAILS_CODE"] = row["DETAILS_CODE"];
                entity["N_PARENTID"] = row["N_PARENTID"];
                entity["N_NUM1"] = row["N_NUM1"];
                entity["N_NUM2"] = row["N_NUM2"];
                entity["n_useamount"] = row["n_useamount"];
                entity["n_amountProbability"] = row["n_amountProbability"];
                t1.Rows.Add(entity);
                Inno(t1, t2, Convert.ToInt32(entity["n_proid"]));
            }
        }

        /// <summary>
        /// 周计划申请
        /// </summary>
        /// <param name="bModle"></param>
        /// <param name="pModel"></param>
        /// <param name="list"></param>
        /// <param name="tModel"></param>
        /// <param name="NexModel"></param>
        /// <returns></returns>
        public int AppPlanWeek(QS_Business bModle, QS_PLANWEEKAPPLY pModel, List<QS_PLANWEEK_DETAIL> list, Lc_Transaction tModel, Lc_Transaction NexModel, string logname)
        {
            List<string> cList = new List<string>();

            string sql = @"insert into qs_business
  (n_business_id,
   n_business_name,
   n_process_id,
   n_business_state,
   n_business_operator_id,
   n_business_operator_time,
   n_remark,
   n_company_id)
values
  (" + bModle.Business_Id + @",
   '" + bModle.Business_Name + @"',
   " + bModle.Process_id + @",
  " + bModle.Business_State + @",
   " + bModle.Business_operator_Id + @",
   sysdate,
 '" + bModle.Business_remark + @"',
      " + bModle.N_COMPANY_ID + @")";

            cList.Add(sql);

            sql = @"update qs_planweekapply
   set
       n_business_id = " + pModel.N_BUSINESS_ID + @",
       n_company_id = " + pModel.N_COMPANY_ID + @",
       n_user_id = " + pModel.N_USER_ID + @",
       d_operator_time = sysdate,
       c_reamrk = '" + pModel.C_REAMRK + @"',
       n_business_state = " + pModel.N_BUSINESS_STATE + @",
       d_start = TO_DATE('" + pModel.D_START.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),
       d_end = TO_DATE('" + pModel.D_END.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD')
 where n_weekapp_id = " + pModel.N_WEEKAPP_ID + "";
            cList.Add(sql);

            sql = @"delete qs_planweek_detail
 where N_WEEKAPP_ID = " + pModel.N_WEEKAPP_ID + "";

            cList.Add(sql);

            for (int i = 0; i < list.Count; i++)
            {
                #region 明细

                QS_PLANWEEK_DETAIL dModel = list[i];

                sql = @"
insert into qs_planweek_detail
  (n_weekdetail_id,
   n_weekapp_id,
   n_proid,
   c_proname,
   n_amount,
   c_remark,
   n_operatorid,
   d_operatortime,
   n_business_id,
   n_type,
   n_syamount,
   n_useamount,
   c_proweeknum,N_MONTHAMOUNT,
   C_PRONUM,N_TYPE2,N_IF_DETAILS,C_DETAILS_CODE,N_NUM1,N_NUM2,N_PARENTID,N_AMOUNT1)
values
  (SEQ_WEEKDETAIL_ID.Nextval,
   " + dModel.N_WEEKAPP_ID + @",
   " + dModel.N_PROID + @",
   '" + dModel.C_PRONAME + @"',
   " + dModel.N_AMOUNT + @",
   '" + dModel.C_REMARK + @"',
   " + dModel.N_OPERATORID + @",
   sysdate,
  " + dModel.N_BUSINESS_ID + @",
  " + dModel.N_TYPE + @",
   " + dModel.N_SYAMOUNT + @",
  " + dModel.N_USEAMOUNT + @",'" + dModel.C_PROWEEKNUM + @"'," + dModel.N_MONTHAMOUNT + @",'" + dModel.C_PRONUM + @"',
  " + dModel.N_TYPE2 + @"," + dModel.N_IF_DETAILS + @",'" + dModel.C_DETAILS_CODE + @"',
   " + dModel.N_NUM1 + "," + dModel.N_NUM2 + @"," + dModel.N_PARENTID + @"," + dModel.N_AMOUNT1 + @")";

                cList.Add(sql);

                #endregion 明细
            }

            #region MyRegion

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + tModel.N_PROCESS_ID + @",
   " + tModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + tModel.N_TRANSACTION_STATE + @",
   '" + tModel.C_TRANSACTION_COMMENT + @"',
   '" + tModel.C_TRANSACTOR_ID + @"',
   sysdate,
   sysdate,
   TO_DATE('" + tModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + tModel.N_TRANSACTION_DUE_STATE + @",
   '" + tModel.C_TRANSACTOR_SIGNATURE + @"',
   " + tModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + NexModel.N_PROCESS_ID + @",
   " + NexModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + NexModel.N_TRANSACTION_STATE + @",
   '" + NexModel.C_TRANSACTION_COMMENT + @"',
   '" + NexModel.C_TRANSACTOR_ID + @"',
   sysdate,
   TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('" + NexModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + NexModel.N_TRANSACTION_DUE_STATE + @",
   '" + NexModel.C_TRANSACTOR_SIGNATURE + @"',
   " + NexModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);

            #endregion MyRegion

            int rec = base.ThkService.SQLSExcute(cList, "周计划申请", "AppPlanWeek", logname, logname);
            return rec;
        }

        public override bool Insert(QS_PLANWEEKAPPLY entity)
        {
            return base.Insert(entity);
        }

        public Tuple<string, List<Tuple<string, string, string>>> InsertSqlP(QS_PLANWEEKAPPLY entity)
        {
            entity.N_WEEKAPP_ID = GetSequence("SEQ_WEEKAPP_ID");

            var sql = @"INSERT INTO QS_PLANWEEKAPPLY(
                            N_WEEKAPP_ID,
                            N_YEARAPP_ID,
                            N_MONTHAPP_ID,
                            N_YEAR,
                            N_MONTH,
                            N_WEEK,
                            C_USEWEEK,
                            N_BUSINESS_ID,
                            C_WEEK_NAME,
                            N_COMPANY_ID,
                            N_USER_ID,
                            D_OPERATOR_TIME,
                            C_REAMRK,
                            N_BUSINESS_STATE,
                            D_START,
                            D_END)
                        VALUES(
                            :N_WEEKAPP_ID,
                            :N_YEARAPP_ID,
                            :N_MONTHAPP_ID,
                            :N_YEAR,
                            :N_MONTH,
                            :N_WEEK,
                            :C_USEWEEK,
                            :N_BUSINESS_ID,
                            :C_WEEK_NAME,
                            :N_COMPANY_ID,
                            :N_USER_ID,
                            :D_OPERATOR_TIME,
                            :C_REAMRK,
                            :N_BUSINESS_STATE,
                            :D_START,
                            :D_END)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_WEEKAPP_ID", entity.N_WEEKAPP_ID, "int");
            parms.Add(":N_YEARAPP_ID", entity.N_YEARAPP_ID, "int");
            parms.Add(":N_MONTHAPP_ID", entity.N_MONTHAPP_ID, "int");
            parms.Add(":N_YEAR", entity.N_YEAR, "int");
            parms.Add(":N_MONTH", entity.N_MONTH, "int");
            parms.Add(":N_WEEK", entity.N_WEEK, "int");
            parms.Add(":C_USEWEEK", entity.C_USEWEEK, "string");
            parms.Add(":N_BUSINESS_ID", entity.N_BUSINESS_ID, "int");
            parms.Add(":C_WEEK_NAME", entity.C_WEEK_NAME, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            parms.Add(":N_USER_ID", entity.N_USER_ID, "int");
            parms.Add(":D_OPERATOR_TIME", entity.D_OPERATOR_TIME, "datetime");
            parms.Add(":C_REAMRK", entity.C_REAMRK, "string");
            parms.Add(":N_BUSINESS_STATE", entity.N_BUSINESS_STATE, "int");
            parms.Add(":D_START", entity.D_START, "datetime");
            parms.Add(":D_END", entity.D_END, "datetime");
            return new Tuple<string, List<Tuple<string, string, string>>>(sql, parms);
        }

        public override bool Update(QS_PLANWEEKAPPLY entity)
        {
            var sql = "UPDATE QS_PLANWEEKAPPLY SET N_YEARAPP_ID=" + entity.N_YEARAPP_ID;
            sql += ",N_MONTHAPP_ID=" + entity.N_MONTHAPP_ID;
            sql += ",N_YEAR=" + entity.N_YEAR;
            sql += ",N_MONTH=" + entity.N_MONTH;
            sql += ",N_WEEK=" + entity.N_WEEK;
            sql += ",C_USEWEEK='" + (entity.C_USEWEEK == "" ? " " : entity.C_USEWEEK) + "'";
            sql += ",N_BUSINESS_ID=" + entity.N_BUSINESS_ID;
            sql += ",C_WEEK_NAME='" + (entity.C_WEEK_NAME == "" ? " " : entity.C_WEEK_NAME) + "'";
            sql += ",N_COMPANY_ID=" + entity.N_COMPANY_ID;
            sql += ",N_USER_ID=" + entity.N_USER_ID;
            sql += ",D_OPERATOR_TIME=TO_DATE('" + entity.D_OPERATOR_TIME.ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS')";
            sql += ",C_REAMRK='" + (entity.C_REAMRK == "" ? " " : entity.C_REAMRK) + "'";
            sql += ",N_BUSINESS_STATE=" + entity.N_BUSINESS_STATE;
            sql += ",D_START=TO_DATE('" + entity.D_START.ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS')";
            sql += ",D_END=TO_DATE('" + entity.D_END.ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS')";
            sql += " WHERE N_WEEKAPP_ID=" + entity.N_WEEKAPP_ID;
            return NoQuery(sql) > 0 ? true : false;
        }

        public override QS_PLANWEEKAPPLY Find(object key)
        {
            var entitys = Where("N_WEEKAPP_ID = " + key.ToString());
            if (entitys.Count > 0)
            {
                return entitys[0];
            }
            return null;
        }

        public QS_PLANWEEKAPPLY FindByBuid(int bid)
        {
            var sql = "SELECT * FROM QS_PLANWEEKAPPLY T1 WHERE T1.N_BUSINESS_ID=:N_BUSINESS_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_BUSINESS_ID", bid, "int");
            var dt = Query(sql, parms);
            return ConvertEntity(dt).FirstOrDefault();
        }

        public JArray GetData(int applyId)
        {
            //查询数据库是否有金额或者备注不为0或空的
            var sql = @"SELECT
                            T1.*,NULL AS C_IMAGE_URL
                        FROM QS_PLANWEEK_DETAIL T1
                        WHERE
                            T1.N_WEEKAPP_ID = :APPLY_ID
                        ORDER BY T1.N_WEEKDETAIL_ID ASC";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            JArray arry = JArray.FromObject(Query(sql, parms));
            return arry;
            /*
            var sql = "SELECT * FROM QS_PLANWEEKAPPLY T1 WHERE T1.N_WEEKAPP_ID = " + applyId + " AND T1.N_AMOUNT !=0  ORDER BY T1.N_NUM1,T1.N_NUM2";
            JArray arry = JArray.FromObject(Query(sql));

            //若有一条金额不为零或者备注不为空,则使用自动汇总
            if (arry.Count == 0)
            {
                sql = @"SELECT T1.N_PROJECT_ID N_PROID,T1.C_PROJECT_NAME C_PRONAME,SUM(T1.N_MONEY) N_AMOUNT,(CASE WHEN LENGTH(T1.C_REMARK) != 0 THEN GROUP_CONCAT(T2.USER_NAME,':',T1.C_REMARK) ELSE NULL END) C_REMARK,N_TYPE N_TYPE FROM QS_PLANWEEK_DEPARTMENT T1,SYS_USER T2
                       WHERE T1.N_USER_ID = T2.USER_ID AND
                       T1.N_APPLY_ID = " + applyId + " GROUP BY T1.N_PROJECT_ID";
                arry = JArray.FromObject(Query(sql));
            }
            else
            {
                sql = "SELECT * FROM QS_PLANWEEK_DETAIL T1 WHERE T1.N_WEEKAPP_ID = " + applyId + "  ORDER BY T1.N_NUM1,T1.N_NUM2";
                arry = JArray.FromObject(Query(sql));
            }

            ////解析为实体并更新
            //var sql = @"UPDATE QX_PLANYEAR_DETAIL
            //            SET
            //                N_AMOUNT = ?N_MONEY,
            //                C_REMARK = ?C_REMARK
            //            WHERE
            //                N_YEARAPP_ID = ?N_APPLY_ID AND
            //                N_PROID = ?N_PROJECT_ID";
            //var entitys = new List<QX_PLANYEAR_DETAIL>();
            //foreach (JObject item in arry)
            //{
            //    var parms = new List<Tuple<string, string, string>>();
            //    parms.Add("?N_MONEY", item["N_MONEY"].Equals(JValue.CreateNull())?0:item["N_MONEY"].ToObject<decimal>());
            //    parms.Add("?C_REMARK", item["C_REMARK"].Equals(JValue.CreateNull())?null:item["C_REMARK"].ToObject<string>());
            //    parms.Add("?N_APPLY_ID", applyId);
            //    parms.Add("?N_PROJECT_ID", item["N_PROJECT_ID"].ToObject<int>());
            //    NoQuery(sql, parms);
            //}
            return arry;
            */
        }

        #region IApplyWeeekService 成员

        public IResonResult CreateOrFind(int userId, int companyId, int year, int month, int week)
        {
            var companyBll = new Sys_CompanyBLL();
            var planWeekDetailBll = new QS_PLANWEEK_DETAIL_BLL();
            //查询主表 是否创建周计划
            var sql = "SELECT COUNT(*) FROM QS_PLANWEEKAPPLY T1 WHERE T1.N_YEAR=" + year;
            sql += " AND T1.N_COMPANY_ID=" + companyId;
            sql += " AND T1.N_MONTH=" + month;
            sql += " AND T1.N_WEEK=" + week;
            sql += " AND T1.N_BUSINESS_STATE in(1,2)";
            if (GetCount(sql) > 0)
            {
                return new ResonResult("当前周已制作周资金计划");
            }
            else
            {
                sql = "SELECT T1.* FROM QS_PLANWEEKAPPLY T1 WHERE N_BUSINESS_STATE<>0 and T1.N_YEAR=" + year;
                sql += " AND T1.N_COMPANY_ID=" + companyId;
                sql += " AND T1.N_MONTH=" + month;
                sql += " AND T1.N_WEEK=" + week;
                //  sql += " AND T1.N_USER_ID="+userId;
                var dt = Query(sql);
                var entity = ConvertEntity(dt).FirstOrDefault();
                if (entity == null)
                {
                    entity = new QS_PLANWEEKAPPLY();
                    //查询当前计划月
                    sql = "SELECT * FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_YEAR=" + year;
                    sql += " AND T1.N_COMPANY_ID=" + companyId;
                    sql += " AND T1.N_MONTH=" + month;
                    sql += " AND T1.N_BUSINESS_STATE=2";
                    dt = Query(sql);
                    if (dt.Rows.Count == 0)
                    {
                        return new ResonResult("没有做月资金计划,请先做月资金计划");
                    }
                    var applyMonthBll = new QS_PLANMONTHAPPLYBLL();
                    var applyMonthEntity = applyMonthBll.ConvertEntity(dt).FirstOrDefault();

                    entity.N_YEARAPP_ID = applyMonthEntity.N_YEARAPP_ID;
                    entity.N_MONTHAPP_ID = applyMonthEntity.N_MONTHAPP_ID;
                    entity.N_YEAR = year;
                    entity.N_MONTH = month;
                    entity.N_WEEK = week;
                    entity.C_USEWEEK = year.ToString() + month.ToString().PadLeft(2, '0') + week.ToString().PadLeft(2, '0');

                    entity.N_BUSINESS_ID = 0;
                    var company = companyBll.Find(companyId);
                    entity.C_WEEK_NAME = year + "年" + month + "月" + "第" + week + "周计划编制(" + company.CMY_NAME + ")";
                    entity.N_COMPANY_ID = companyId;
                    entity.N_USER_ID = userId;
                    entity.D_OPERATOR_TIME = DateTime.Now;
                    entity.C_REAMRK = " ";
                    entity.N_BUSINESS_STATE = -1;
                    entity.D_START = DateTime.Parse("1900-1-1");
                    entity.D_END = DateTime.Parse("1900-1-1");
                    Insert(entity);
                    //把字表添加数据
                    var itemEntitys = new List<QS_PLANWEEK_DETAIL>();
                    DataTable dt1 = GetDataSyBind(companyId.ToString(), companyId.ToString(), year.ToString(), month.ToString(), week.ToString(), "");
                    if (dt1 == null || dt1.Rows.Count == 0)
                    {
                        //无本公司特殊项目，取通用项目
                        dt1 = GetDataSyBind(companyId.ToString(), "0", year.ToString(), month.ToString(), week.ToString(), "");
                    }

                    foreach (DataRow row in dt1.Rows)
                    {
                        QS_PLANWEEK_DETAIL entity1 = new QS_PLANWEEK_DETAIL();
                        entity1.N_WEEKAPP_ID = entity.N_WEEKAPP_ID;
                        entity1.N_PROID = decimal.Parse(row["n_proid"].ToString());
                        entity1.C_PRONAME = row["c_proname"].ToString();
                        entity1.N_AMOUNT = 0;
                        entity1.C_REMARK = " ";
                        entity1.N_OPERATORID = userId;
                        entity1.D_OPERATORTIME = DateTime.Now;
                        entity1.N_BUSINESS_ID = 0;
                        entity1.N_TYPE = Convert.ToDecimal(row["n_type"].ToString());//decimal.Parse(row["n_type"].ToString());
                        entity1.N_SYAMOUNT = 0;
                        entity1.N_USEAMOUNT = 0;
                        entity1.C_PROWEEKNUM = company.C_CODE + entity.C_USEWEEK + entity1.N_PROID.ToString();

                        entity1.C_PRONUM = row["C_PRONUM"].ToString();
                        entity1.N_TYPE2 = decimal.Parse(row["N_TYPE2"].ToString());

                        entity1.N_IF_DETAILS = row["IF_DETAILS"].ToString() == "" ? 0 : decimal.Parse(row["IF_DETAILS"].ToString());

                        entity1.C_DETAILS_CODE = row["DETAILS_CODE"].ToString();
                        entity1.N_NUM1 = decimal.Parse(row["N_NUM1"].ToString());
                        entity1.N_NUM2 = decimal.Parse(row["N_NUM2"].ToString());
                        entity1.N_PARENTID = decimal.Parse(row["n_parentid"].ToString());

                        entity1.N_AMOUNT1 = 0;
                        entity1.N_AMOUNT2 = 0;
                        entity1.C_REMARK2 = "";
                        planWeekDetailBll.Insert(entity1);
                    }
                }
                return new ResonResult("获取成功", ResultType.success, entity);
            }
        }

        #endregion IApplyWeeekService 成员

        public bool Create(DateTime applyDate, int weekIndex, DateTime beginDate, DateTime overDate, int companyId, string title, List<int> applyEditUserIdArry, out QS_PLANWEEKAPPLY apply, out string Msg)
        {
            var sql = "";
            var parms = new List<Tuple<string, string, string>>();
            apply = null;
            Msg = "";
            try
            {
                #region MyRegion

                var bll = new QX_PLANYEARAPPLYBLL(this.Service);
                var applyDetailBll = new QX_PLANYEAR_DETAILBLL(bll.Service);
                var applyMonthBll = new QS_PLANMONTHAPPLYBLL(bll.Service);
                var applyMonthDetailsBll = new QS_PLANMONTH_DETAILBLL(bll.Service);
                var businessBll = new Qs_BusinessBLL(bll.Service);
                var companyBll = new Sys_CompanyBLL(bll.Service);
                var processBll = new Lc_ProcessBLL(bll.Service);
                var queueBll = new Lc_QueueBLL(bll.Service);
                var conditionBll = new Lc_ConditionBLL(bll.Service);
                var nodeBll = new Lc_NodeBLL(bll.Service);
                var transactionBll = new Lc_TransactionBLL(bll.Service);
                var projectBll = new QS_PROJECTBLL(bll.Service);
                var planYearDetailsBll = new QX_PLANYEAR_DETAILBLL(bll.Service);

                #endregion MyRegion

                var company = companyBll.Find(companyId);

                #region 判断年资金是否申请

                var applyYear = bll.Find(applyDate.Year, companyId);
                if (applyYear == null)
                {
                    applyYear = new QX_PLANYEARAPPLY();
                    applyYear.N_YEARAPP_ID = 0;
                    applyYear.N_YEAR = applyDate.Year;
                    applyYear.N_BUSINESS_ID = 0;
                    applyYear.C_YEARAPP_NAME = applyDate.Year + "年资金计划";
                    applyYear.N_COMPANY_ID = companyId;
                    applyYear.N_USER_ID = User.USER_ID;
                    applyYear.D_OPERATOR_TIME = DateTime.Now;
                    applyYear.C_REAMRK = "系统自动添加";
                    applyYear.N_BUSINESS_STATE = 2;
                    bll.Insert(applyYear);
                    applyDetailBll.LoadByApplyId((int)applyYear.N_YEARAPP_ID, 0);
                }

                #endregion 判断年资金是否申请

                #region 判断月资金是否已申请

                var applyMonth = applyMonthBll.Find(companyId, applyDate);
                if (applyMonth == null)
                {
                    applyMonth = new QS_PLANMONTHAPPLY();
                    applyMonth.N_MONTHAPP_ID = 0;
                    applyMonth.N_YEAR = applyDate.Year;
                    applyMonth.N_MONTH = applyDate.Month;
                    applyMonth.C_USEMONTH = applyDate.ToString("yyyy-MM");
                    applyMonth.N_BUSINESS_ID = 0;
                    applyMonth.C_MONTH_NAME = applyDate.ToString("yyyy年MM月资金计划");
                    applyMonth.N_COMPANY_ID = companyId;
                    applyMonth.N_USER_ID = User.USER_ID;
                    applyMonth.D_OPERATOR_TIME = DateTime.Now;
                    applyMonth.C_REAMRK = "系统自动添加";
                    applyMonth.N_BUSINESS_STATE = 2;
                    applyMonth.N_YEARAPP_ID = applyYear.N_YEARAPP_ID;
                    applyMonthBll.Insert(applyMonth);

                    #region MyRegion

                    parms = new List<Tuple<string, string, string>>();
                    //载入 初始数据
                    sql = @"SELECT
                        T1.N_IF_DETAILS AS IF_DETAILS,
                        T1.C_DETAILS_CODE AS DETAILS_CODE,
                        T1.N_PROID AS PROJECT_ID,
                        T1.C_PRONAME AS PROJECT_NAME,
                        T1.N_AMOUNT AS AMOUNT,
                        T1.C_REMARK AS REMARK,
                        T1.N_PARENTID AS PARENT_PROJECT_ID,
                        T1.N_TYPE AS TYPE_CODE,
                        T1.N_TYPE2 AS TYPE_CODE2,
                        T1.C_PRONUM AS PROJECT_NUMBER,
                        T1.N_NUM1 AS ORDER_NUMBER1,
                        T1.N_NUM2 AS ORDER_NUMBER2,
                        (
                            SELECT
                                nvl(SUM(nvl(TT2.N_AMOUNT,0)),0)
                            FROM QS_PLANMONTHAPPLY TT1
                            INNER JOIN QS_PLANMONTH_DETAIL TT2 ON TT1.N_MONTHAPP_ID = TT2.N_MONTHAPP_ID
                            WHERE
                                TT1.N_BUSINESS_STATE = 2 AND
                                TT1.N_COMPANY_ID =:COMPANY_ID AND
                                TT2.N_PROID = T1.N_PROID AND
                                TT1.N_YEAR = :YEAR AND
                                TT1.N_MONTH < :MONTH
                        ) AS APPLY_AMOUNT,
                        (
                            SELECT
                                nvl(SUM(nvl(TT3.N_USEAMOUNT,0)),0)
                            FROM QS_PLANMONTHAPPLY TT1
                            INNER JOIN QS_PLANWEEKAPPLY TT2 ON TT1.N_MONTHAPP_ID = TT2.N_MONTHAPP_ID
                            INNER JOIN QS_PLANWEEK_DETAIL TT3 ON TT3.N_WEEKAPP_ID = TT2.N_WEEKAPP_ID
                            WHERE
                                TT1.N_BUSINESS_STATE = 2 AND
                                TT2.N_BUSINESS_STATE = 2 AND
                                TT1.N_COMPANY_ID =:COMPANY_ID AND
                                TT3.N_PROID = T1.N_PROID AND
                                TT1.N_YEAR = :YEAR AND
                                TT1.N_MONTH < :MONTH
                        ) AS USE_AMOUNT
                        FROM QX_PLANYEAR_DETAIL T1
                    WHERE
                    T1.N_YEARAPP_ID = :APPLY_ID
                    ORDER BY N_YDETAIL_ID ASC";
                    parms.Clear();
                    parms.Add(":COMPANY_ID", companyId, "int");
                    parms.Add(":YEAR", applyDate.Year, "int");
                    parms.Add(":MONTH", applyDate.Month, "int");
                    parms.Add(":APPLY_ID", applyYear.N_YEARAPP_ID, "int");

                    foreach (var item in Query(sql, parms).AsEnumerable())
                    {
                        sql = "INSERT INTO QS_PLANMONTH_DETAIL(N_MDETAIL_ID,N_MONTHAPP_ID,N_PROID,C_PRONAME,N_AMOUNT,C_REMARK,N_OPERATORID,D_OPERATORTIME,N_BUSINESS_ID,N_TYPE,N_SYAMOUNT,N_USEAMOUNT,C_PRONUM,N_TYPE2,N_IF_DETAILS,C_DETAILS_CODE,N_NUM1,N_NUM2,N_PARENTID,N_AMOUNT1,N_AMOUNT2,C_REMARK2) VALUES(:N_MDETAIL_ID,:N_MONTHAPP_ID,:N_PROID,:C_PRONAME,:N_AMOUNT,:C_REMARK,:N_OPERATORID,:D_OPERATORTIME,:N_BUSINESS_ID,:N_TYPE,:N_SYAMOUNT,:N_USEAMOUNT,:C_PRONUM,:N_TYPE2,:N_IF_DETAILS,:C_DETAILS_CODE,:N_NUM1,:N_NUM2,:N_PARENTID,:N_AMOUNT1,:N_AMOUNT2,:C_REMARK2)";
                        parms = new List<Tuple<string, string, string>>();
                        parms.Add(":N_MDETAIL_ID", GetSequence("SEQ_MDETAIL_ID"), "int");
                        parms.Add(":N_MONTHAPP_ID", applyMonth.N_MONTHAPP_ID, "int");
                        parms.Add(":N_PROID", item.GetValue<int>("PROJECT_ID"), "int");
                        parms.Add(":C_PRONAME", item.GetValue<string>("PROJECT_NAME"), "string");
                        parms.Add(":N_AMOUNT", 0, "decimal");
                        parms.Add(":C_REMARK", "", "string");
                        parms.Add(":N_OPERATORID", User.USER_ID, "int");
                        parms.Add(":D_OPERATORTIME", DateTime.Now, "datetime");
                        parms.Add(":N_BUSINESS_ID", 0, "int");
                        parms.Add(":N_TYPE", item.GetValue<int>("TYPE_CODE"), "int");

                        parms.Add(":N_SYAMOUNT", item.GetValue<int>("APPLY_AMOUNT"), "decimal");
                        parms.Add(":N_USEAMOUNT", item.GetValue<int>("USE_AMOUNT"), "decimal");
                        parms.Add(":C_PRONUM", item.GetValue<string>("PROJECT_NUMBER"), "string");
                        parms.Add(":N_TYPE2", item.GetValue<int>("TYPE_CODE2"), "int");
                        parms.Add(":N_IF_DETAILS", item.GetValue<int?>("IF_DETAILS"), "int");
                        parms.Add(":C_DETAILS_CODE", item.GetValue<string>("DETAILS_CODE"), "string");
                        parms.Add(":N_NUM1", item.GetValue<int>("ORDER_NUMBER1"), "int");
                        parms.Add(":N_NUM2", item.GetValue<int>("ORDER_NUMBER2"), "int");
                        parms.Add(":N_PARENTID", item.GetValue<int>("PARENT_PROJECT_ID"), "decimal");
                        parms.Add(":N_AMOUNT1", null, "decimal");
                        parms.Add(":N_AMOUNT2", null, "decimal");
                        parms.Add(":C_REMARK2", "", "string");
                        NoQuery(sql, parms);
                    }

                    #endregion MyRegion

                    //Msg = applyDate.ToString("yyyy年MM月(" + company.CMY_NAME + ") 未申请月资金计划!");
                    //return false;
                }

                #endregion 判断月资金是否已申请

                sql = @"SELECT
                            COUNT(1)
                        FROM QS_PLANWEEKAPPLY T1
                        WHERE
                            T1.N_BUSINESS_STATE IN(1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID AND
                            T1.C_USEWEEK = :USE_WEEK";
                parms = new List<Tuple<string, string, string>>();
                parms.Add(":COMPANY_ID", companyId, "int");
                parms.Add(":USE_WEEK", applyDate.ToString("yyyyMM0" + weekIndex), "string");
                if (GetCount(sql, parms) > 0)
                {
                    Msg = applyDate.ToString("yyyy年MM月第" + weekIndex + "周(" + company.CMY_NAME + ") 已经申请了周资金计划!");
                    return false;
                }

                #region MyRegion

                /*
                        //判断选择的周期是否有效
                        //查询当前月最后一周结束时间
                        sql = @"SLEECT
                                 MAX(T1.D_END)
                                FROM QS_PLANWEEKAPPLY T1
                                WHERE
                                    T1.N_YEAR = ?YEAR AND
                                    T1.N_MONTH = ?MONTH AND
                                    T1.N_WEEK < ?
                            ";
                        if (true)
                        {
                        }*/

                #endregion MyRegion

                List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                var process = processBll.GetProcessByCom("QS_PLANWEEKAPPLY", User.USER_NAME);
                if (process == null)
                {
                    Msg = "未设置周资金计划流程";
                    return false;
                }
                var business = new QS_Business();
                business.Business_Name = applyDate.ToString("yyyyMM0" + weekIndex) + "周计划编制（" + company.CMY_NAME + "）";
                business.Process_id = process.N_PROCESS_ID;// 121;
                business.Business_State = 1;
                business.Business_operator_Id = (int)User.USER_ID;
                business.Business_remark = title.Trim();
                business.N_COMPANY_ID = User.USER_COMPANY;
                business.Business_operator_date = DateTime.Now;
                sqlArry.Add(businessBll.InsertSqlP(business));

                //新增主表
                apply = new QS_PLANWEEKAPPLY();
                apply.N_YEARAPP_ID = applyMonth.N_YEARAPP_ID;
                apply.N_MONTHAPP_ID = applyMonth.N_MONTHAPP_ID;
                apply.N_YEAR = applyDate.Year;
                apply.N_MONTH = applyDate.Month;
                apply.N_WEEK = weekIndex;
                apply.C_USEWEEK = applyDate.ToString("yyyyMM0" + weekIndex);
                apply.N_BUSINESS_ID = business.Business_Id;
                apply.C_WEEK_NAME = applyDate.ToString("yyyyMM") + "（" + company.CMY_NAME + "）";
                apply.N_COMPANY_ID = companyId;
                apply.N_USER_ID = User.USER_ID;
                apply.C_REAMRK = title.Trim();
                apply.D_OPERATOR_TIME = DateTime.Now;
                apply.N_BUSINESS_STATE = 1;
                apply.D_START = beginDate;
                apply.D_END = overDate;
                sqlArry.Add(this.InsertSqlP(apply));
                Lc_Transaction transaction = new Lc_Transaction();
                transaction.N_PROCESS_ID = business.Process_id;
                //获取第一步队列id
                transaction.N_QUEUE_ID = 195;
                transaction.N_business_id = business.Business_Id;
                transaction.C_TRANSACTION_COMMENT = "周计划申请";
                transaction.C_TRANSACTOR_ID = User.USER_ID.ToString();
                transaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                transaction.D_TRANSACTION_TIME_END = DateTime.Now;
                transaction.D_TRANSACTION_TIME_DUE = DateTime.Parse("1900-1-1");
                transaction.N_TRANSACTION_STATE = 1;
                transaction.C_TRANSACTOR_SIGNATURE = "";
                transaction.N_LAST_BACE_TRANSACTION_ID = 0;
                transaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                transaction.D_TRANSACTION_TIME_END = DateTime.Now;
                sqlArry.Add(transactionBll.InsertSqlP(transaction));
                {
                    if (applyEditUserIdArry.Count > 0)
                    {
                        Lc_Transaction nextTransaction = new Lc_Transaction();
                        nextTransaction.N_PROCESS_ID = business.Process_id;
                        nextTransaction.N_QUEUE_ID = 215;
                        nextTransaction.N_business_id = business.Business_Id;
                        nextTransaction.C_TRANSACTION_COMMENT = "";
                        nextTransaction.C_TRANSACTOR_ID = applyEditUserIdArry.ToIn();
                        nextTransaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                        nextTransaction.D_TRANSACTION_TIME_END = DateTime.Parse("1900-1-1");
                        Lc_Queue queue = queueBll.GetInfoById(nextTransaction.N_QUEUE_ID.ToString());
                        Lc_Node nodel = nodeBll.GetInfoById(queue.N_NODE_ID.ToString());
                        nextTransaction.D_TRANSACTION_TIME_DUE = DateTime.Now.AddDays((int)nodel.N_DOIING_TIME);
                        nextTransaction.N_TRANSACTION_STATE = 0;
                        nextTransaction.C_TRANSACTOR_SIGNATURE = "";
                        nextTransaction.N_LAST_BACE_TRANSACTION_ID = transaction.N_TRANSACTION_ID;
                        sqlArry.Add(transactionBll.InsertSqlP(nextTransaction));
                    }
                }
                {
                    Lc_Transaction nextTransaction = new Lc_Transaction();
                    nextTransaction.N_PROCESS_ID = business.Process_id;
                    nextTransaction.N_QUEUE_ID = 216;
                    nextTransaction.N_business_id = business.Business_Id;
                    nextTransaction.C_TRANSACTION_COMMENT = "";
                    nextTransaction.C_TRANSACTOR_ID = User.USER_ID.ToString();
                    nextTransaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                    nextTransaction.D_TRANSACTION_TIME_END = DateTime.Parse("1900-1-1");
                    Lc_Queue queue = queueBll.GetInfoById(nextTransaction.N_QUEUE_ID.ToString());
                    Lc_Node nodel = nodeBll.GetInfoById(queue.N_NODE_ID.ToString());
                    nextTransaction.D_TRANSACTION_TIME_DUE = DateTime.Now.AddDays((int)nodel.N_DOIING_TIME);
                    nextTransaction.N_TRANSACTION_STATE = 0;
                    nextTransaction.C_TRANSACTOR_SIGNATURE = "";
                    nextTransaction.N_LAST_BACE_TRANSACTION_ID = transaction.N_TRANSACTION_ID;
                    sqlArry.Add(transactionBll.InsertSqlP(nextTransaction));
                }
                //载入 初始数据
                sql = @"SELECT
                            T1.N_IF_DETAILS AS IF_DETAILS,
                            T1.C_DETAILS_CODE AS DETAILS_CODE,
                            T1.N_PROID AS PROJECT_ID,
                            T1.C_PRONAME AS PROJECT_NAME,
                            T1.N_AMOUNT AS AMOUNT,
                            T1.C_REMARK AS REMARK,
                            T1.N_PARENTID AS PARENT_PROJECT_ID,
                            T1.N_TYPE AS TYPE_CODE,
                            T1.N_TYPE2 AS TYPE_CODE2,
                            T1.C_PRONUM AS PROJECT_NUMBER,
                            T1.N_NUM1 AS ORDER_NUMBER1,
                            T1.N_NUM2 AS ORDER_NUMBER2,
                            (
                                SELECT
                                    nvl(SUM(nvl(TT2.N_AMOUNT,0)),0)
                                FROM QS_PLANWEEKAPPLY TT1
                                INNER JOIN QS_PLANWEEK_DETAIL TT2 ON TT1.N_WEEKAPP_ID = TT2.N_WEEKAPP_ID
                                WHERE
                                    TT1.N_BUSINESS_STATE = 2 AND
                                    TT1.N_COMPANY_ID =:COMPANY_ID AND
                                    TT2.N_PROID = T1.N_PROID AND
                                    TT1.N_YEAR = :YEAR AND
                                    TT1.N_MONTH = :MONTH AND
                                    TT1.N_WEEK < :WEEK
                            ) AS APPLY_AMOUNT,
                            (
                                SELECT
                                    nvl(SUM(nvl(TT2.N_USEAMOUNT,0)),0)
                                FROM QS_PLANWEEKAPPLY TT1
                                INNER JOIN QS_PLANWEEK_DETAIL TT2 ON TT1.N_WEEKAPP_ID = TT2.N_WEEKAPP_ID
                                WHERE
                                    TT1.N_BUSINESS_STATE = 2 AND
                                    TT1.N_COMPANY_ID =:COMPANY_ID AND
                                    TT2.N_PROID = T1.N_PROID AND
                                    TT1.N_YEAR = :YEAR AND
                                    TT1.N_MONTH = :MONTH AND
                                    TT1.N_WEEK < :WEEK
                            ) AS USE_AMOUNT,
                            nvl(T1.N_AMOUNT,0) AS MONTH_AMOUNT
                        FROM QS_PLANMONTH_DETAIL T1
                    WHERE
                        T1.N_MONTHAPP_ID = :APPLY_ID
                        ORDER BY T1.N_MDETAIL_ID ASC";
                parms = new List<Tuple<string, string, string>>();
                parms.Add(":APPLY_ID", applyMonth.N_MONTHAPP_ID, "int");
                parms.Add(":YEAR", applyDate.Year, "int");
                parms.Add(":MONTH", applyDate.Month, "int");
                parms.Add(":COMPANY_ID", companyId, "int");
                parms.Add(":WEEK", weekIndex, "int");
                foreach (var item in Query(sql, parms).AsEnumerable())
                {
                    sql = @"INSERT INTO QS_PLANWEEK_DETAIL(
                                N_WEEKDETAIL_ID,
                                N_WEEKAPP_ID,
                                N_PROID,
                                C_PRONAME,
                                N_AMOUNT,
                                C_REMARK,
                                N_OPERATORID,
                                D_OPERATORTIME,
                                N_BUSINESS_ID,
                                N_TYPE,
                                N_SYAMOUNT,
                                N_USEAMOUNT,
                                N_MONTHAMOUNT,
                                C_PROWEEKNUM,
                                C_PRONUM,
                                N_TYPE2,
                                N_IF_DETAILS,
                                C_DETAILS_CODE,
                                N_NUM1,
                                N_NUM2,
                                N_PARENTID,
                                N_AMOUNT1,
                                N_AMOUNT2,
                                C_REMARK2)
                            VALUES(
                                :N_WEEKDETAIL_ID,
                                :N_WEEKAPP_ID,
                                :N_PROID,
                                :C_PRONAME,
                                :N_AMOUNT,
                                :C_REMARK,
                                :N_OPERATORID,
                                :D_OPERATORTIME,
                                :N_BUSINESS_ID,
                                :N_TYPE,
                                :N_SYAMOUNT,
                                :N_USEAMOUNT,
                                :N_MONTHAMOUNT,
                                :C_PROWEEKNUM,
                                :C_PRONUM,
                                :N_TYPE2,
                                :N_IF_DETAILS,
                                :C_DETAILS_CODE,
                                :N_NUM1,
                                :N_NUM2,
                                :N_PARENTID,
                                :N_AMOUNT1,
                                :N_AMOUNT2,
                                :C_REMARK2)";
                    parms = new List<Tuple<string, string, string>>();
                    parms.Add(":N_WEEKDETAIL_ID", GetSequence("SEQ_WEEKDETAIL_ID"), "int");
                    parms.Add(":N_WEEKAPP_ID", apply.N_WEEKAPP_ID, "int");
                    parms.Add(":N_PROID", item.GetValue<int>("PROJECT_ID"), "int");
                    parms.Add(":C_PRONAME", item.GetValue<string>("PROJECT_NAME"), "string");
                    parms.Add(":N_AMOUNT", 0, "decimal");
                    parms.Add(":C_REMARK", "", "string");
                    parms.Add(":N_OPERATORID", User.USER_ID, "int");
                    parms.Add(":D_OPERATORTIME", DateTime.Now, "datetime");
                    parms.Add(":N_BUSINESS_ID", business.Business_Id, "int");
                    parms.Add(":N_TYPE", item.GetValue<int>("TYPE_CODE"), "int");
                    parms.Add(":N_SYAMOUNT", item.GetValue<int>("APPLY_AMOUNT"), "decimal");
                    parms.Add(":N_USEAMOUNT", item.GetValue<int>("USE_AMOUNT"), "decimal");
                    parms.Add(":N_MONTHAMOUNT", item.GetValue<int>("MONTH_AMOUNT"), "decimal");
                    if (string.IsNullOrEmpty(company.C_CODE))
                    {
                        Msg = company.CMY_NAME + "没有设置公司代码，请设置公司代码后进行资金申请";
                        return false;
                    }
                    //银行流水码
                    parms.Add(":C_PROWEEKNUM", company.C_CODE + applyDate.ToString("yyyyMM") + (weekIndex >= 10 ? weekIndex.ToString() : "0" + weekIndex.ToString()) + item.GetValue<int>("PROJECT_ID"), "string");
                    parms.Add(":C_PRONUM", item.GetValue<string>("PROJECT_NUMBER"), "string");
                    parms.Add(":N_TYPE2", item.GetValue<int>("TYPE_CODE2"), "int");
                    parms.Add(":N_IF_DETAILS", item.GetValue<int?>("IF_DETAILS"), "int");
                    parms.Add(":C_DETAILS_CODE", item.GetValue<string>("DETAILS_CODE"), "string");
                    parms.Add(":N_NUM1", item.GetValue<int>("ORDER_NUMBER1"), "int");
                    parms.Add(":N_NUM2", item.GetValue<int>("ORDER_NUMBER2"), "int");
                    parms.Add(":N_PARENTID", item.GetValue<int>("PARENT_PROJECT_ID"), "decimal");
                    parms.Add(":N_AMOUNT1", null, "decimal");
                    parms.Add(":N_AMOUNT2", null, "decimal");
                    parms.Add(":C_REMARK2", "", "string");
                    sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }
                using (var conn = GetDbConn())
                {
                    conn.Open();
                    this.Service.DbConnection = conn;
                    using (var tran = conn.BeginTransaction())
                    {
                        try
                        {
                            SQLSExcute(sqlArry);
                            tran.Commit();
                        }
                        catch (Exception)
                        {
                            tran.Rollback();
                            throw;
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Msg = "异常";
                return false;
                //throw;
            }
        }

        public void Update(JArray arry, int applyId)
        {
            //解析为实体并更新
            var sql = @"UPDATE QS_PLANWEEK_DETAIL
                        SET
                            N_AMOUNT = :N_MONEY,
                            C_REMARK = :C_REMARK,
                            N_AMOUNT1 = :N_MONEY,
                            C_REMARK2 = :C_REMARK
                        WHERE
                            N_WEEKAPP_ID = :N_APPLY_ID AND
                            N_PROID = :N_PROJECT_ID";
            var entitys = new List<QX_PLANYEAR_DETAIL>();
            var sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
            foreach (JObject item in arry)
            {
                var parms = new List<Tuple<string, string, string>>();
                parms.Add(":N_MONEY", item["Money"].ToObject<decimal?>() == null ? 0 : item["Money"].ToObject<decimal>());
                parms.Add(":C_REMARK", item["Remark"].ToObject<string>() == null ? "" : item["Remark"].ToObject<string>());
                parms.Add(":N_APPLY_ID", applyId);
                parms.Add(":N_PROJECT_ID", item["ProjectId"].ToObject<int>());
                //NoQuery(sql, parms);
                sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
            }
            this.SQLSExcute(sqlArry);
        }

        private void Run()
        {
            var sql = @"SELECT
	T2.N_WEEKAPP_ID AS WEEK_APP_ID,
	T1.N_WEEKDETAIL_ID AS WEEK_APP_DETAILS_ID,
	T1.N_PROID AS PRO_ID,
	T2.D_END AS END_DATE,
	T4.C_ACCNO1 AS ACCNO_IN,
	T4.C_ACCNO2 AS ACCNO_OUT,
	T4.N_AMT AS MONEY,
	T4.N_DETAIL_LISTID AS DETAIL_LISTID
FROM QS_PLANWEEK_DETAIL T1
INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T1.N_PROID = T3.N_PROID
INNER JOIN YX_BANK_DETAILLIST T4 ON T2.D_START <= T4.D_TRANDATE AND T2.D_END >= T4.D_TRANDATE
INNER JOIN YX_ACCOUNT T5 ON T4.C_ACCNO1 = T5.ACCOUNTNO AND T2.N_COMPANY_ID = T5.COMPANY_ID
INNER JOIN QS_PROJECT_CODE T6 ON T3.N_PROID = T6.N_PROID AND T6.C_CODE = T4.C_DET AND T4.N_STATE = 0 AND T2.N_BUSINESS_STATE=2
WHERE
	rownum <= 100";
            var dt = Query(sql);
            foreach (var item in dt.AsEnumerable())
            {
                var parms = new List<Tuple<string, string, string>>();
                Run2(item.GetValue<int>("WEEK_APP_ID"), item.GetValue<int>("PRO_ID"), item.GetValue<decimal>("MONEY"));
                sql = @"UPDATE YX_BANK_DETAILLIST T1
SET
	T1.N_PROID = :PRO_ID,
	T1.N_WEEKDETAIL_ID = :WEEK_DETAILS_ID,
	T1.N_STATE = 1
WHERE
	T1.N_DETAIL_LISTID = :DETAIL_LISTID";
                parms.Clear();
                parms.Add(":PRO_ID", item.GetValue<int>("PRO_ID"), "int");
                parms.Add(":WEEK_DETAILS_ID", item.GetValue<int>("WEEK_APP_DETAILS_ID"), "int");
                parms.Add(":DETAIL_LISTID", item.GetValue<int>("DETAIL_LISTID"), "int");
                NoQuery(sql, parms);
            }
        }

        private void Go()
        {
            using (var conn = GetDbConn())
            {
                conn.Open();
                this.Service.DbConnection = conn;
                while (CurrentBankCount() > 0)
                {
                    Run1();
                }
            }
        }

        private void Run1()
        {
            using (var tran = this.Service.DbConnection.BeginTransaction())
            {
                try
                {
                    Run();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    //写入异常产生日志
                    LogHelper.GetLogHelper().WriteLog("银行流水匹配异常");
                }
            }
        }

        private void Run2(int applyId, int proid, decimal money)
        {
            var sql = @"UPDATE QS_PLANWEEK_DETAIL T1
SET

    T1.N_USEAMOUNT = T1.N_USEAMOUNT +:MONEY
   WHERE
    T1.N_WEEKAPP_ID = :WEEK_APP_ID AND
    T1.N_PROID = :PRO_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":WEEK_APP_ID", applyId, "int");
            parms.Add(":PRO_ID", proid, "int");
            parms.Add(":MONEY", money / 10000, "decimal");
            NoQuery(sql, parms);
            sql = @"SELECT
                        T2.N_PROID AS PRO_ID
                    FROM QS_PLANWEEK_DETAIL T1
                    INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID AND T1.N_PARENTID = T2.N_PROID
                    WHERE
                        T1.N_PROID = :PRO_ID AND
	                    T1.N_WEEKAPP_ID = :WEEK_APP_ID";
            parms.Clear();
            parms.Add(":WEEK_APP_ID", applyId, "int");
            parms.Add(":PRO_ID", proid, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count > 0)
            {
                Run2(applyId, dt.Rows[0].GetValue<int>("PRO_ID"), money);
            }
        }

        private int CurrentBankCount()
        {
            var sql = @"SELECT
    COUNT(1)
FROM QS_PLANWEEK_DETAIL T1
INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T1.N_PROID = T3.N_PROID
INNER JOIN YX_BANK_DETAILLIST T4 ON T2.D_START <= T4.D_TRANDATE AND T2.D_END >= T4.D_TRANDATE
INNER JOIN YX_ACCOUNT T5 ON T4.C_ACCNO1 = T5.ACCOUNTNO AND T2.N_COMPANY_ID = T5.COMPANY_ID
INNER JOIN QS_PROJECT_CODE T6 ON T3.N_PROID = T6.N_PROID AND T6.C_CODE = T4.C_DET AND T4.N_STATE = 0 AND T2.N_BUSINESS_STATE=2";
            return GetCount(sql);
        }

        public static void BankRun()
        {
            var thread = new Thread(new ThreadStart(delegate ()
            {
                while (true)
                {
                    new QS_PLANWEEKAPPLYBLL().Go();
                    //每执行一次停止10秒
                    Thread.Sleep(1000 * 10);
                }
            }));
            thread.Start();
        }

        /// <summary>
        /// 获取周资金信息(主营业营收)
        /// </summary>
        public DataTable GetData_1(int companyId, int year, int month, int week)
        {
            var sql = @"SELECT
                            T2.*
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL1 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        WHERE
                            T1.N_YEAR = :YEAR AND
                            T1.N_MONTH = :MONTH AND
                            T1.N_WEEK = :WEEK AND
                            T1.N_BUSINESS_STATE IN (1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":WEEK", week, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取周资金信息(天燃气购入)
        /// </summary>
        public DataTable GetData_2(int companyId, int year, int month, int week)
        {
            var sql = @"SELECT
                            T2.*
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL2 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        WHERE
                            T1.N_YEAR = :YEAR AND
                            T1.N_MONTH = :MONTH AND
                            T1.N_WEEK = :WEEK AND
                            T1.N_BUSINESS_STATE IN (1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":WEEK", week, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取周资金信息(proType:1安装外包费用;2材料费用;3大修费用4城市管网项目)
        /// </summary>
        public DataTable GetData_3(int companyId, int year, int month, int week, int proType)
        {
            var sql = @"SELECT
                            T2.*
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL3 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        WHERE
                            T1.N_YEAR = :YEAR AND
                            T1.N_MONTH = :MONTH AND
                            T1.N_WEEK = :WEEK AND
                            T1.N_BUSINESS_STATE IN (1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID AND
                            T2.N_PROTYPE = :PROTYPE";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":WEEK", week, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            parms.Add(":PROTYPE", proType, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取周资金信息
        /// </summary>
        public DataTable GetData(int companyId, int year, int month, int week)
        {
            var sql = @"SELECT
                            T2.*
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
                        WHERE
                            T1.N_YEAR = :YEAR AND
                            T1.N_MONTH = :MONTH AND
                            T1.N_WEEK = :WEEK AND
                            T1.N_BUSINESS_STATE IN (1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID
                        ORDER BY T2.N_WEEKDETAIL_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":WEEK", week, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 判断资金申请是否存在
        /// </summary>
        public QS_PLANWEEKAPPLY GetEntity(int companyId, int year, int month, int week)
        {
            var sql = @"SELECT
                            T1.*
                        FROM QS_PLANWEEKAPPLY T1
                        WHERE
                            T1.N_YEAR = :YEAR AND
                            T1.N_MONTH = :MONTH AND
                            T1.N_WEEK = :WEEK AND
                            T1.N_BUSINESS_STATE IN (1,2) AND
                            T1.N_COMPANY_ID = :COMPANY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":WEEK", week, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            var entity = new QS_PLANWEEKAPPLY();
            var row = dt.Rows[0];
            entity.N_WEEKAPP_ID = row.GetValue<int>("N_WEEKAPP_ID");
            entity.N_YEARAPP_ID = row.GetValue<int>("N_YEARAPP_ID");
            entity.N_MONTHAPP_ID = row.GetValue<int>("N_MONTHAPP_ID");
            entity.N_YEAR = row.GetValue<int>("N_YEAR");
            entity.N_MONTH = row.GetValue<int>("N_MONTH");
            entity.N_WEEK = row.GetValue<int>("N_WEEK");
            entity.C_USEWEEK = row.GetValue<string>("C_USEWEEK");
            entity.N_BUSINESS_ID = row.GetValue<int>("N_BUSINESS_ID");
            entity.C_WEEK_NAME = row.GetValue<string>("C_WEEK_NAME");
            entity.N_COMPANY_ID = row.GetValue<int>("N_COMPANY_ID");
            entity.N_USER_ID = row.GetValue<int>("N_USER_ID");
            entity.D_OPERATOR_TIME = row.GetValue<DateTime>("D_OPERATOR_TIME");
            entity.C_REAMRK = row.GetValue<string>("C_REAMRK");
            entity.N_BUSINESS_STATE = row.GetValue<int>("N_BUSINESS_STATE");
            entity.D_START = row.GetValue<DateTime>("D_START");
            entity.D_END = row.GetValue<DateTime>("D_END");
            return entity;
        }

        /// <summary>
        /// 批量修改周计划明细(计划)
        /// </summary>
        /// <param name="list"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Updates1(List<QS_PLANWEEK_DETAIL> list, string logname)
        {
            List<string> cList = new List<string>();

            string sql = "";
            for (int i = 0; i < list.Count; i++)
            {
                QS_PLANWEEK_DETAIL model = list[i];
                sql = @" update QS_PLANWEEK_DETAIL
   set n_amount = " + model.N_AMOUNT + @",N_AMOUNT1=" + model.N_AMOUNT1 + @", C_REMARK= '" + model.C_REMARK + @"',
D_OPERATORTIME=sysdate
 where N_WEEKDETAIL_ID = " + model.N_WEEKDETAIL_ID;

                cList.Add(sql);
                //if (i != list.Count - 1)
                //{
                //    sql += ";";
                //}
            }

            int rec = base.ThkService.SQLSExcute(cList, "批量修改周计划明细", "Updates", logname, logname);
            return rec;
            // return 0;
        }

        /// <summary>
        /// 批量修改周计划明细(审批)
        /// </summary>
        /// <param name="list"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Updates(List<QS_PLANWEEK_DETAIL> list, string logname)
        {
            List<string> cList = new List<string>();

            string sql = "";
            for (int i = 0; i < list.Count; i++)
            {
                QS_PLANWEEK_DETAIL model = list[i];
                sql = @" update QS_PLANWEEK_DETAIL
   set n_amount = " + model.N_AMOUNT + @",N_AMOUNT2=" + model.N_AMOUNT2 + @", C_REMARK2 = '" + model.C_REMARK2 + @"',
D_OPERATORTIME=sysdate
 where N_WEEKDETAIL_ID = " + model.N_WEEKDETAIL_ID;

                cList.Add(sql);
                //if (i != list.Count - 1)
                //{
                //    sql += ";";
                //}
            }

            int rec = base.ThkService.SQLSExcute(cList, "批量修改周计划明细", "Updates", logname, logname);
            return rec;
            // return 0;
        }
    }

    public interface IApplyWeeekService
    {
        IResonResult CreateOrFind(int userId, int companyId, int year, int month, int week);
    }
}